<?php

namespace App\Console\Commands;

use App\Admin\Services\GoodService;
use App\Model\Note;
use Illuminate\Console\Command;
use Illuminate\Support\Facades\DB;

class SyncNote extends Command
{
    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'susan:sync-note';

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = 'Command description';

    /**
     * Create a new command instance.
     *
     * @return void
     */
    public function __construct()
    {
        parent::__construct();
    }

    /**
     * Execute the console command.
     *
     * @return mixed
     */
    public function handle()
    {
        //


        // 子查询builder
        $adminNoteSubQueryBuilder = Note::query()->select(['id', 'goods_id', 'content'])
            ->where('user_user_id', 3)
            ->orderBy('id', 'desc')
            ->limit(4000);

        DB::table(DB::raw("({$adminNoteSubQueryBuilder->toSql()}) as sub"))
            ->mergeBindings($adminNoteSubQueryBuilder->getQuery())
            ->groupBy('goods_id')
            ->orderBy('goods_id')
            ->chunk(100, function ($data) {
                $adminNote = [];
                foreach ($data as $datum) {

                    if ("UTF-8" != mb_detect_encoding($datum->content, array("ASCII", 'UTF-8', "GB2312", "GBK", 'BIG5'))) {
                        $datum->content = GoodService::convertToUtf($datum->content);
                    }

                    $adminNote[] = "($datum->goods_id,'$datum->content')";
                }
                if ($adminNote) {
                    $this->insert(['goods_id', 'admin_note'], $adminNote, ['admin_note']);
                }

            });


        // 子查询builder
        $customNoteSubQueryBuilder = Note::query()->select(['id', 'goods_id', 'content'])
            ->where('user_user_id', "!=", 3)
            ->orderBy('id', 'desc')
            ->limit(4000);

        DB::table(DB::raw("({$customNoteSubQueryBuilder->toSql()}) as sub"))
            ->mergeBindings($customNoteSubQueryBuilder->getQuery())
            ->groupBy('goods_id')
            ->orderBy('goods_id')->chunk(100, function ($data) {
                $customNote = [];
                foreach ($data as $datum) {

                    if ("UTF-8" != mb_detect_encoding($datum->content, array("ASCII", 'UTF-8', "GB2312", "GBK", 'BIG5'))) {
                        $datum->content = GoodService::convertToUtf($datum->content);
                    }

                    $customNote[] = "($datum->goods_id,'$datum->content')";
                }
                if ($customNote) {
                    $this->insert(['goods_id', 'custom_note'], $customNote, ['custom_note']);
                }

            });

    }


    public function insert(array $filed, array $insertRaw, array $duplicateValues)
    {
        $duplicate = [];
        foreach ($duplicateValues as $value) {
            $duplicate[] = "`$value` = VALUES($value)";
        }
        $sql = "insert into good_note (" . implode(',', $filed) . ") " .
            "VALUES" . implode(',', $insertRaw) .
            " ON DUPLICATE KEY UPDATE " . implode(',', $duplicate);
        DB::insert($sql);
    }
}
